﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;
using ECCSystem.BizRule;
using ECC_EXT_DAO.Dao;
using ECC_EXT_Entity;
using Excel;
using DataTable = System.Data.DataTable;

namespace ECCSystem
{
    public partial class frm_Ext_Setting_Category : Form
    {
        object objMissing = System.Reflection.Missing.Value;
        public frm_Ext_Setting_Category()
        {
            InitializeComponent();
        }

        private void btn_Import_Click(object sender, EventArgs e)
        {

            if (string.IsNullOrEmpty(txt_FilePath.Text.Trim()))
            {
                MessageBox.Show("请选择要导入的文件!");
                return;
            }

            string strFlg = ImportExcel(txt_FilePath.Text.Trim());

            if (strFlg == "")
            {
                MessageBox.Show("データ導入成功！", "ECC Information");

            }
            else if (strFlg == "1")
            {
                MessageBox.Show("データを読み取る時、導入不可能", "ECC Information");
            }
            else
            {
                MessageBox.Show(strFlg, "ECC Catch Error");
            }
        }

        private string ImportExcel(string strFilePath)
        {
            Excel.Application app = new Excel.Application();
            Workbook objWorkBook = null;
            Worksheet objWorkSheet = null;

            DICCategoryDao _DICCategoryDao = new DICCategoryDao();
            DICCategoryEntity _entity;
            try
            {
                objWorkBook = app.Workbooks.Open(strFilePath, objMissing, objMissing, objMissing, objMissing, objMissing,
                                            objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);

                objWorkSheet = (Worksheet)objWorkBook.Sheets[1];
                if (objWorkSheet.Name != "カタログ")
                {
                    return "1";
                }

                _DICCategoryDao.DeleteAll();

                int cnt = 0;
                while (true)
                {
                    _entity = new DICCategoryEntity();
                    
                    var tmp = objWorkSheet.Cells[2 + cnt, "A"] as Range;

                    if (tmp.Value2 == null)
                        break;
                    else
                        _entity.CategoryName =
                            tmp.Value2.ToString();


                    tmp = objWorkSheet.Cells[2 + cnt, "B"] as Range;

                    if (tmp.Value2 == null)
                        _entity.Type = 0;
                    else
                        _entity.Type = int.Parse(
                            tmp.Value2.ToString());

                    tmp = objWorkSheet.Cells[2 + cnt++, "C"] as Range;

                    if (tmp.Value2 == null)
                        _entity.Vip = 0;
                    else
                        _entity.Vip = int.Parse(
                            tmp.Value2.ToString());
                    _entity.ID = cnt;
                    _DICCategoryDao.SaveOrUpdate(_entity);

                }

                return "";
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
            finally
            {
                objWorkSheet = null;
                if (objWorkBook != null)
                    objWorkBook.Close(objMissing, objMissing, objMissing);
                if (app != null)
                    ExtUtility.Kill(app);
                System.GC.Collect();
            }
        }

        private DataSet objDS;
        private void btn_Export_Click(object sender, EventArgs e)
        {
            var objBRReports = new ClsReports();
            objDS = (DataSet)objBRReports.GetSettingCategory();

            this.CreateExcel(objDS.Tables[0]);
        }

        private void CreateExcel(DataTable dt)
        {
            Excel.ApplicationClass objExlApp = new Excel.ApplicationClass();
            Excel.Workbook theWookbook = null;
            Excel.Worksheet theWookSheet = null;
            var objCommon = new ECCSystem.BizRule.ClsCommon();
            try
            {
                objExlApp.Visible = false;

                object objMissing = System.Reflection.Missing.Value;
                StringBuilder strSavePath = new StringBuilder();
                strSavePath.Append("C:\\ECCReport\\");
                //if strSavePath is not existing,create the path
                if (Directory.Exists(strSavePath.ToString()) == false)
                {
                    Directory.CreateDirectory(strSavePath.ToString());
                }

                StringBuilder strTemplatePath = new StringBuilder();
                //Excel template path
                strTemplatePath.Append(System.Windows.Forms.Application.StartupPath);
                strTemplatePath.Append("\\Ext\\ReportTemplate\\Ext_Setting_Category.xls");
                //if this template is existing or not
                if (File.Exists(strTemplatePath.ToString()) == false)
                {
                    MessageBox.Show("レポートのテンプレートファイルが見つかりません。", "ECC Information");
                    //quit excel object
                    objCommon.QuitExcelApp(objExlApp, theWookbook, theWookSheet, null);
                    System.GC.Collect();
                    //this.btn_Export.Enabled = true;
                    return;
                }
                //open the excel template
                theWookbook = objExlApp.Workbooks.Open(strTemplatePath.ToString(), objMissing, objMissing, objMissing, objMissing, objMissing,
                                         objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);
                //get the first sheet
                theWookSheet = (Excel.Worksheet)theWookbook.Worksheets[1];

                //fill data into excel


                int baseIdx = 0;
                Excel.Range rg;

                for (var k = 0; k < dt.Rows.Count; k++)
                {
                    theWookSheet.Range["A" + (2 + k)].Value2 = dt.Rows[k]["VCategoryName"].ToString();
                    theWookSheet.Range["B" + (2 + k)].Value2 = dt.Rows[k]["VOnline"].ToString();
                    theWookSheet.Range["C" + (2 + k)].Value2 = dt.Rows[k]["VVip"].ToString();
                }

                //set excel report save path and name
                strSavePath.Append("Setting_Category");


                if (File.Exists(strSavePath.ToString() + ".xls") == true)
                {
                    File.Delete(strSavePath.ToString() + ".xls");
                }

                //if the report has existed or not
                if (File.Exists(strSavePath.ToString()) == true)
                {
                    MessageBox.Show("このファイルがもう存在しました！", "ECC Information");
                    //quit excel object
                    objCommon.QuitExcelApp(objExlApp, theWookbook, theWookSheet, null);
                    System.GC.Collect();
                    //btnExcel.Enabled = true;
                    return;
                }

                //save the report
                theWookbook.SaveAs(strSavePath.ToString(), objMissing, objMissing, objMissing, objMissing, objMissing,
                    Excel.XlSaveAsAccessMode.xlNoChange, objMissing, objMissing, objMissing, objMissing);
                //prompt save success
                MessageBox.Show("操作成功しました。", "ECC Information");

                //quit excel object
                objCommon.QuitExcelApp(objExlApp, theWookbook, theWookSheet, null);
                System.GC.Collect();
                this.Close();
            }
            catch (Exception ex)
            {
                //quit excel object
                objCommon.QuitExcelApp(objExlApp, theWookbook, theWookSheet, null);
                System.GC.Collect();

                //MessageBox.Show(ex.ToString(), "ECC Catch Error");
                //btnExcel.Enabled = true;
            }
        }

        private void btn_Folder_Click(object sender, EventArgs e)
        {
            OpenFileDialog objFileDialog = new OpenFileDialog();
            //string strFileName = "";

            string strFilePath = "";
            objFileDialog.Title = "";
            objFileDialog.Multiselect = false;
            objFileDialog.AddExtension = true;
            objFileDialog.Filter = "Excel Worksheets|*.xls|All files (*.*)|*.*";
            objFileDialog.InitialDirectory = "C:\\";

            if (objFileDialog.ShowDialog(this) == DialogResult.OK)
            {

                strFilePath = objFileDialog.FileName;
                //strFileName = objFileDialog.SafeFileName;

                if (!strFilePath.ToLower().Contains(".xls"))
                {
                    MessageBox.Show("ファイルは選択ミスで、導入不可能", "ECC Information");
                    return;
                }
                txt_FilePath.Text = strFilePath;

                btn_Import.Enabled = true;
            }
        }













    }
}
